# _*_ coding: UTF-8 _*_
# @Time     : 2020/10/16 下午 04:09
# @Author   : Li Jie
# @Site     : http://www.cdtest.cn/
# @File     : mysql_test.py
# @Software : PyCharm

import pymysql


# 数据库的操作
# 查询数据
def read_db():
    # 1.打开数据库连接
    connect = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test')

    # 2.生成游标
    cursor = connect.cursor()

    # 3.执行sql语句
    sql = 'select * from t_students;'
    cursor.execute(sql)  # 单条执行

    # 4.获取查询结果集
    resultSet = cursor.fetchall()
    print(resultSet)

    # 5. 提交合并事务
    connect.commit()

    # 6.关闭游标和数据库连接
    cursor.close()
    connect.close()

    return resultSet


# 新增数据
def write_db():
    # 1.打开数据库连接
    connect = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test')

    # 2.生成游标
    cursor = connect.cursor()

    # 3.执行sql语句
    sql = 'insert into t_students values (4,"王五","男","南京");'
    cursor.execute(sql)

    # 4.提交事务
    connect.commit()

    # 5.关闭数据库连接
    cursor.close()
    connect.close()


def db_mysql(sql):
    # 1.打开数据库连接
    connect = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test')

    # 2.生成游标
    cursor = connect.cursor()

    # 3.执行sql语句
    cursor.execute(sql)

    # 4.获取查询结果集
    resultSet = cursor.fetchall()

    # 5. 提交事务
    connect.commit()

    # 6.关闭数据库连接
    cursor.close()
    connect.close()

    return resultSet


# 批量执行sql语句
def db_mysql_execute_many():
    # 1.打开数据库连接
    connect = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test')

    # 2.生成游标
    cursor = connect.cursor()

    # 3. 准备数据序列
    data = [
        [5, 'test1', '男', 'xxxx'],
        [6, 'test2', '男', 'xxxx'],
        [7, 'test3', '男', 'xxxx'],
        [8, 'test4', '男', 'xxxx'],
        [9, 'test5', '男', 'xxxx'],
    ]

    # 4. 批量执行sql语句
    cursor.executemany('INSERT INTO t_students(id,name,sex,address) VALUES (%s,%s,%s,%s);', data)

    # 5.获取查询结果集
    resultSet = cursor.fetchall()

    # 6. 提交事务
    connect.commit()

    # 7.关闭数据库连接
    cursor.close()
    connect.close()

    return resultSet


if __name__ == '__main__':
    # read_db()
    # write_db()
    # rs = db('select * from students;')
    # print(rs)
    # rs = db_mysql('insert into t_students values (5,"王六","男","贵阳");')
    # print(rs)

    db_mysql_execute_many()
    read_db()
